package com.example.demo1;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xwpf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author zhangzichang
 * @Description: 解析新疆自治区/兵团运输信息工程数据库word文档，并把列注释保存到数据库中
 * @company：CTTIC
 * @since 2021/4/25 17:20
 */
public class WordUtil {
    public static final String TABLE_COMMENT_SQL = " COMMENT ON TABLE %s IS '%s' ";
    public static final String COLUMN_COMMENT_SQL = " COMMENT ON COLUMN %s.%s IS '%s' ";

    public static void main(String[] args) throws IOException, SQLException {
        String fileName = "E:\\company\\project\\新疆自治区-兵团运输项目\\新疆运政数据字典.docx";
        FileInputStream fs = new FileInputStream(new File(fileName));
//        String text = "2.2. 2 从业人员_资格证(PR_PracCertificate)";
//        System.out.println(pathTableName(text));
        p(fs, null, true, false);
//        List<IBodyElement> bodyElements = wordDocument.getBodyElements();
//        for(IBodyElement bodyElement:bodyElements){
//            BodyElementType elementType = bodyElement.getElementType();
//            System.out.println(elementType.name());
//            if(StringUtils.equals("PARAGRAPH",elementType.name())){
//                XWPFDocument document = (XWPFDocument)bodyElement.getBody();
//                List<XWPFParagraph> paragraphs = document.getParagraphs();
//            }else if(StringUtils.equals("TABLE",elementType.name())){
//                XWPFTable table = (XWPFTable) bodyElement.getBody();
//                System.out.println(table.getText());
//            }

//        }

//        List<XWPFTable> tables = wordDocument.getTables();
//        for(XWPFTable xwpfTable : tables){
//            List<XWPFTableRow> rows = xwpfTable.getRows();
//            XWPFTableRow xwpfTableRow = rows.get(0);
//            List<XWPFTableCell> tableCells = xwpfTableRow.getTableCells();
//            System.out.println(tableCells.stream().map(XWPFTableCell::getText).collect(Collectors.joining("\t")));
//        }
//        System.out.println(tables.size());

    }

    /**
     * @Description 解析word文档，生成对应的数据表和列备注信息
     * @Author zhangzichang
     * @Date 2021/4/28 9:30
     * @Company CTTIC
     * @Param [fs, statement, tabaleFlg, columnFlg]
     *  fs : 解析文件流
     *  statement : sql_statement
     *  tabaleFlg : 是否生成table备注
     *  columnFlg : 是否生成column备注
     * @return void
     **/
    public static void p(FileInputStream fs, Statement statement, boolean tabaleFlg, boolean columnFlg) throws IOException, SQLException {
        //开始加载word文档
        XWPFDocument wordDocument = new XWPFDocument(fs);
        System.out.println(wordDocument);
        Iterator<IBodyElement> bodyElementsIterator = wordDocument.getBodyElementsIterator();
        //定义文本和表格初始下标
        AtomicInteger paragraphIndex = new AtomicInteger(0);
        AtomicInteger tableIndex = new AtomicInteger(0);
        //定义所有文本列表和表格列表对象信息
        List<String> databaseTableList = new ArrayList<>();
        boolean isInitTable = false;
        List<XWPFParagraph> paragraphs = null;
        List<XWPFTable> tables = null;
        String curTableName = null;
        boolean isCommentFlg = false;
        List<XWPFTableRow> rows = null;
        while (bodyElementsIterator.hasNext()){
            IBodyElement next = bodyElementsIterator.next();
//            System.out.println(next.getElementType());
            //初始化所有的文本信息和表格信息
            if(CollectionUtils.isEmpty(paragraphs) && CollectionUtils.isEmpty(tables)){
                paragraphs = next.getBody().getParagraphs();
                tables = next.getBody().getTables();
            }

            if(StringUtils.equals(BodyElementType.PARAGRAPH.name(), next.getElementType().name())){
                XWPFParagraph xwpfParagraph = paragraphs.get( paragraphIndex.getAndIncrement() );
                String text = xwpfParagraph.getText();
                String pathTableName = pathTableName(text);
                if(StringUtils.isNotEmpty(pathTableName)){
                    curTableName = pathTableName;
                    System.out.println("--当前正在处理表：" + curTableName);
                    isCommentFlg = false;
                }
            }else if(StringUtils.equals(BodyElementType.TABLE.name(), next.getElementType().name())){
                if(tableIndex.get()==0){
                    tableIndex.getAndIncrement();
                    continue;
                }
                XWPFTable xwpfTable = tables.get(tableIndex.getAndIncrement());
//                System.out.println(xwpfTable.getRows().size());
                if(!isInitTable){//初始化数据库表, 表名别名
                    rows = xwpfTable.getRows();
                    for(int i=1; i<rows.size(); i++){
                        XWPFTableRow xwpfTableRow = rows.get(i);
                        XWPFTableCell tableName = xwpfTableRow.getCell(1);
                        XWPFTableCell tableAlias = xwpfTableRow.getCell(2);
                        databaseTableList.add(tableName.getText());
                        String commentTableSql = String.format(TABLE_COMMENT_SQL, tableName.getText(), tableAlias.getText());
                        System.out.println(commentTableSql);
                        if(statement != null){
                            statement.execute(commentTableSql);
                        }
                    }
                    isInitTable = true;
                }else if(columnFlg && !isCommentFlg){
                    rows = xwpfTable.getRows();
                    for(int i=1; i<rows.size(); i++){
                        XWPFTableRow xwpfTableRow = rows.get(i);
                        XWPFTableCell columnAlias = xwpfTableRow.getCell(1);
                        XWPFTableCell columnName = xwpfTableRow.getCell(2);
                        String commentColumnSql = String.format(COLUMN_COMMENT_SQL, curTableName, columnName.getText(), columnAlias.getText());
                        System.out.println(commentColumnSql);
                        if(statement != null){
                            statement.execute(commentColumnSql);
                        }
                    }
                    isCommentFlg = true;
                }
            }

        }
        wordDocument.close();
    }

    public static String pathTableName(String text){
        Pattern p= Pattern.compile("(?<=\\()[a-zA-Z_]+(?=\\))");
        Matcher m=p.matcher(text);
        if(m.find()){
            return m.group();
        }
        return StringUtils.EMPTY;
    }
}
